package com.kaikeba.dao.imp;

import com.kaikeba.bean.Express;
import com.kaikeba.bean.User;
import com.kaikeba.dao.BaseUserDao;
import com.kaikeba.util.DruidUtil;

import java.lang.reflect.Array;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class UserDaoMysql implements BaseUserDao {
    //用于查询数据库中的全部用户数（总数+今日新增）
   public static final String SQL_CONSOLE ="SELECT " +
            "COUNT(ID) user_total," +
            "COUNT(TO_DAYS(LOGINTIME)=TO_DAYS(NOW()) OR NULL ) user_day  " +
            "FROM EUSERS";
    //用于查询所有用户信息
    public static final String SQL_FIND_ALL ="SELECT * FROM EUSERS";
    public static final String SQL_FIND_LIMIT ="SELECT * FROM EUSERS LIMIT ?,?";
    //根据手机号，查询用户信息
    public static final String SQL_FIND_BY_PHONE ="SELECT * FROM EUSERS WHERE USERPHONE=?";
    //根据用户手机号码，查询他所有的快递信息
    public static final String SQL_FIND_EXPRESS_BY_PHONE ="SELECT * FROM EXPRESS WHERE USERPHONE=? and status=?";
    // 用户的录入
    public static final String SQL_INSERT ="INSERT INTO EUSERS VALUES(NULL,?,?,?,?,NOW(),NULL,1,null)";
    //用户的修改
    public static final String SQL_UPDATE ="UPDATE EUSERS SET NUMBER=?,USERNAME=?,USERPHONE=?,PASSWORD=? WHERE ID=? ";
    //根据ID，删除单个快递信息
    public static final String SQL_DELETE ="DELETE FROM EUSERS WHERE ID=?";
    //刷新登录的IP与时间
    private static final String SQL_UPDATE_LOGIN_TIME = "UPDATE EUSERS SET INTIME=?,LOGINIP=? WHERE USERPHONE=?";
    /**
     * 根据手机号，更新登陆时间和登录ip
     * @param userPhone
     * @param date
     * @param ip
     */
    @Override
    public void updateLoginTime(String userPhone, java.util.Date date, String ip) {
        //1.    获取连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        //2.    预编译SQL语句
        try {
            state = conn.prepareStatement(SQL_UPDATE_LOGIN_TIME);
            //3.    填充参数
            state.setDate(1,new java.sql.Date(date.getTime()));
            state.setString(2,ip);
            state.setString(3,userPhone);
            //4.    执行
            state.executeUpdate();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            //5.    释放资源
            DruidUtil.close(conn,state,null);
        }
    }
    /**
     * 用于查询数据库中的全部用户数（总数+今日新增），
     *
     * @return {user_total:总数,user_day:今日新增}
     */
    @Override
    public Map<String, Integer> console() {
        Map<String,Integer> map = new HashMap<>();
        //1.与数据库建立链接
        Connection connection = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            //2.预加载SQL语句
            assert connection != null;
            statement = connection.prepareStatement(SQL_CONSOLE);
            //3.填充参数可选
            //4.执行SQl语句
            resultSet = statement.executeQuery();
            //5. 获取执行结果
            if(resultSet.next()){
                int user_total = resultSet.getInt("user_total");
                int user_day = resultSet.getInt("user_day");
                map.put("user_total",user_total);
                map.put("user_day",user_day);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //释放资源
            DruidUtil.close(connection,statement,resultSet);
        }
        return map;
    }

    /**
     * 用于查询所有用户
     *
     * @param limit      是否分页的标记，true表示分页。false表示查询所有快递
     * @param offset     SQL语句的起始索引
     * @param pageNumber 页查询的数量
     * @return 快递的集合
     */
    @Override
    public List<User> findAll(boolean limit, int offset, int pageNumber) {
        List<User> userList = new ArrayList<>();
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet =null;
        try {
            if (limit){
                preparedStatement = connection.prepareStatement(SQL_FIND_LIMIT);
                preparedStatement.setInt(1,offset);
                preparedStatement.setInt(2,pageNumber);
            }else {
                preparedStatement = connection.prepareStatement(SQL_FIND_ALL);
            }
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String number = resultSet.getString("number");
                String userPhone = resultSet.getString("userPhone");
                String password = resultSet.getString("password");
                Timestamp loginTime = resultSet.getTimestamp("loginTime");
                Timestamp inTime = resultSet.getTimestamp("inTime");
                int status = resultSet.getInt("status");
                User u = new User(id,number,username,userPhone,password,loginTime,inTime,status);
                userList.add(u);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement,resultSet);
        }
        return userList;
    }

    /**
     * 根据用户手机号，查询用户信息
     * @param userPhone 手机号
     * @return 查询的用户信息，单号不存在时返回null
     */
    @Override
    public User findByPhone(String userPhone) {
        User u = new User();
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            assert connection != null;
            preparedStatement = connection.prepareStatement(SQL_FIND_BY_PHONE);
            preparedStatement.setString(1,userPhone);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()){
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String number = resultSet.getString("number");
                String password = resultSet.getString("password");
                Timestamp loginTime = resultSet.getTimestamp("loginTime");
                Timestamp inTime = resultSet.getTimestamp("inTime");
                int status = resultSet.getInt("status");
                u = new User(id,number,username,userPhone,password,loginTime,inTime,status);
            return u;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement,resultSet);
        }
        return null;
    }

    /**
     * 根据用户手机号码，查询他所有的快递信息
     *
     * @param userPhone 手机号码
     * @param s 所需的状态信息
     * @return 查询的快递信息列表
     */
    @Override
    public List<Express> findByUserPhone(String userPhone,int s) {
        List<Express> expressList = new ArrayList<>();
        //1.    获取数据库的连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        //2.    预编译SQL语句
        try {
            assert conn != null;
            state = conn.prepareStatement(SQL_FIND_EXPRESS_BY_PHONE);
            //3.    填充参数(可选)
            state.setString(1,userPhone);
            state.setInt(2,s);
            //4.    执行SQL语句
            result = state.executeQuery();
            //5.    获取执行的结果
            while (result.next()){
                int id = result.getInt("id");
                String username = result.getString("username");
                String number = result.getString("number");
                String company = result.getString("company");
                String code = result.getString("code");
                Timestamp inTime = result.getTimestamp("inTime");
                Timestamp outTime = result.getTimestamp("outTime");
                int status = result.getInt("status");
                String sysPhone = result.getString("sysPhone");
                Express e = new Express(id,number,username,userPhone,company,code,inTime,outTime,status,sysPhone);
                expressList.add(e);
            }
            //6.    资源的释放
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DruidUtil.close(conn,state,result);
        }
        return expressList;
    }

    /**
     * 用户的录入
     *
     * @param u 要录入的用户对象
     * @return 录入的结果，true表示成功，false表示失败
     */
    @Override
    public boolean insert(User u){
        //1.    连接的获取
        Connection conn = DruidUtil.getConnection();
        //2.    预编译SQL语句
        PreparedStatement state = null;
        try {
            assert conn != null;
            state =conn.prepareStatement(SQL_INSERT);
            state.setString(1,u.getNumber());
            state.setString(2,u.getUsername());
            state.setString(3,u.getUserPhone());
            state.setString(4,u.getPassword());
            return state.executeUpdate()>0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 用户的修改
     * @param id       要修改的快递id
     * @param newUsers 新的快递对象（number，company,username,userPhone）
     * @return 修改的结果，true表示成功，false表示失败
     */
    @Override
    public boolean update(int id, User newUsers) {
        //1.    连接的获取
        Connection conn = DruidUtil.getConnection();
        //2.    预编译SQL语句
        PreparedStatement state = null;
        try {
            assert conn != null;
            state = conn.prepareStatement(SQL_UPDATE);
            state.setString(1,newUsers.getNumber());
            state.setString(2,newUsers.getUsername());
            state.setString(3,newUsers.getUserPhone());
            state.setString(4,newUsers.getPassword());
            state.setInt(5,id);
            return state.executeUpdate()>0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }

    /**
     * 根据id，删除单个快递信息
     *
     * @param id 要删除的快递id
     * @return 删除的结果，true表示成功，false表示失败
     */
    @Override
    public boolean delete(int id) {
        //1.    连接的获取
        Connection conn = DruidUtil.getConnection();
        //2.    预编译SQL语句
        PreparedStatement state = null;
        try {
            assert conn != null;
            state = conn.prepareStatement(SQL_DELETE);
            state.setInt(1,id);
            return state.executeUpdate()>0;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn,state,null);
        }
        return false;
    }
}
